package com.youlai.boot.util;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.ThreadLocalRandom;

/**
 * 简化版供应链数据生成器
 * 
 * <p>独立的数据生成工具，不依赖Spring上下文，可直接运行。
 * 高效生成2023年1月1日至2025年6月9日的供应链报告测试数据。</p>
 * 
 * <h3>主要特性:</h3>
 * <ul>
 *   <li>完全独立，无需Spring Boot环境</li>
 *   <li>JDBC批处理，高效数据生成</li>
 *   <li>模拟真实业务场景数据</li>
 *   <li>包含季节性和趋势性波动</li>
 * </ul>
 * 
 * <h3>使用方法:</h3>
 * <pre>
 * // 修改数据库连接信息后直接运行main方法
 * java com.youlai.boot.util.SimpleDataGenerator
 * </pre>
 * 
 * @author xiheforever
 * @since 2025-01-06
 */
public class SimpleDataGenerator {

    // 数据库连接配置 - 请根据实际情况修改
    private static final String DB_URL = "jdbc:mysql://localhost:3306/youlai_boot?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true";
    private static final String DB_USERNAME = "root";
    private static final String DB_PASSWORD = "q&sQrH#$3ix7";

    // 批处理大小
    private static final int BATCH_SIZE = 1000;

    // 日期格式化器
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    // 店铺配置
    private static final List<String> STORE_NAMES = Arrays.asList(
            "北京旗舰店", "上海中心店", "广州分店", "深圳分店", "成都分店",
            "杭州分店", "南京分店", "武汉分店", "西安分店", "重庆分店"
    );

    private static final double[] STORE_SCALE_FACTORS = {
            1.5, 2.0, 1.3, 1.2, 0.8, 1.1, 0.9, 1.0, 0.7, 1.4
    };

    // 日期范围
    private static final LocalDate START_DATE = LocalDate.of(2023, 1, 1);
    private static final LocalDate END_DATE = LocalDate.of(2025, 6, 9);

    /**
     * 主方法 - 直接运行数据生成
     */
    public static void main(String[] args) {
        System.out.println("🚀 开始执行供应链数据生成任务...");
        long startTime = System.currentTimeMillis();

        try (Connection conn = getConnection()) {
            SimpleDataGenerator generator = new SimpleDataGenerator();
            
            // 设置批处理模式
            conn.setAutoCommit(false);
            
            // 清空现有数据
            generator.clearExistingData(conn);
            
            // 生成新数据
            generator.generateBatchData(conn);
            
            // 提交事务
            conn.commit();
            
            // 验证结果
            generator.validateData(conn);
            
            long duration = System.currentTimeMillis() - startTime;
            System.out.println("🎉 数据生成完成！总耗时: " + duration + "ms");
            
        } catch (Exception e) {
            System.err.println("❌ 数据生成失败: " + e.getMessage());
            e.printStackTrace();
        }
        
        System.out.println("✅ 供应链数据生成任务完成！");
    }

    /**
     * 获取数据库连接
     */
    private static Connection getConnection() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new SQLException("MySQL驱动未找到", e);
        }
        
        return DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
    }

    /**
     * 清空现有数据
     */
    private void clearExistingData(Connection conn) throws SQLException {
        System.out.println("🧹 正在清空现有数据...");
        
        try (Statement stmt = conn.createStatement()) {
            int deletedRows = stmt.executeUpdate("DELETE FROM supply_chain_report");
            System.out.println("✅ 已删除 " + deletedRows + " 条现有记录");
            
            // 重置自增ID
            try {
                stmt.execute("ALTER TABLE supply_chain_report AUTO_INCREMENT = 1");
                System.out.println("✅ 已重置表的自增ID");
            } catch (SQLException e) {
                System.out.println("⚠️ 重置自增ID失败，但不影响数据生成: " + e.getMessage());
            }
        }
    }

    /**
     * 批量生成数据
     */
    private void generateBatchData(Connection conn) throws SQLException {
        System.out.println("📊 开始生成供应链报告数据...");
        System.out.println("📅 数据范围: " + START_DATE + " 至 " + END_DATE);
        System.out.println("🏪 店铺数量: " + STORE_NAMES.size());
        
        long totalDays = START_DATE.until(END_DATE).getDays() + 1;
        long totalRecords = totalDays * STORE_NAMES.size();
        System.out.println("📈 预计生成记录数: " + totalRecords + " 条");

        String insertSql = """
            INSERT INTO supply_chain_report (
                store_name, report_date, spot_quantity, in_transit_quantity, total_quantity,
                spot_amount, in_transit_amount, total_amount, spot_turnover_days, 
                with_in_transit_turnover_days, nr_target, ordered_amount, 
                achievement_rate, pv_spot_rate, is_deleted, create_time, update_time
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW(), NOW())
            """;

        try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
            int batchCount = 0;
            long processedRecords = 0;

            // 遍历每一天
            for (LocalDate currentDate = START_DATE; !currentDate.isAfter(END_DATE); currentDate = currentDate.plusDays(1)) {
                // 为每个店铺生成当天的数据
                for (int storeIndex = 0; storeIndex < STORE_NAMES.size(); storeIndex++) {
                    setRecordParameters(pstmt, currentDate, storeIndex);
                    pstmt.addBatch();
                    batchCount++;

                    // 当批次达到指定大小时，执行批量插入
                    if (batchCount >= BATCH_SIZE) {
                        pstmt.executeBatch();
                        conn.commit();
                        processedRecords += batchCount;
                        
                        // 输出进度
                        if (processedRecords % (BATCH_SIZE * 10) == 0) {
                            double progress = (double) processedRecords / totalRecords * 100;
                            System.out.println("⏳ 已处理: " + processedRecords + " 条记录 (" + String.format("%.1f", progress) + "%)");
                        }
                        
                        batchCount = 0;
                    }
                }
            }

            // 处理剩余记录
            if (batchCount > 0) {
                pstmt.executeBatch();
                conn.commit();
                processedRecords += batchCount;
            }

            System.out.println("✅ 数据生成完成，共处理 " + processedRecords + " 条记录");
        }
    }

    /**
     * 设置单条记录的参数
     */
    private void setRecordParameters(PreparedStatement pstmt, LocalDate reportDate, int storeIndex) throws SQLException {
        String storeName = STORE_NAMES.get(storeIndex);
        double scaleFactor = STORE_SCALE_FACTORS[storeIndex];
        
        // 计算波动因子
        double seasonalFactor = calculateSeasonalFactor(reportDate);
        double trendFactor = calculateTrendFactor(reportDate);
        double randomFactor = 0.8 + ThreadLocalRandom.current().nextDouble() * 0.4;
        double combinedFactor = scaleFactor * seasonalFactor * trendFactor * randomFactor;
        
        // 生成基础数据
        int baseSpotQuantity = (int) (1000 * combinedFactor);
        int spotQuantity = Math.max(500, baseSpotQuantity + ThreadLocalRandom.current().nextInt(-200, 300));
        int inTransitQuantity = (int) (spotQuantity * (0.2 + ThreadLocalRandom.current().nextDouble() * 0.3));
        int totalQuantity = spotQuantity + inTransitQuantity;
        
        // 金额计算
        double unitPrice = 30 + ThreadLocalRandom.current().nextDouble() * 10;
        BigDecimal spotAmount = BigDecimal.valueOf(spotQuantity * unitPrice / 1000).setScale(2, BigDecimal.ROUND_HALF_UP);
        BigDecimal inTransitAmount = BigDecimal.valueOf(inTransitQuantity * unitPrice / 1000).setScale(2, BigDecimal.ROUND_HALF_UP);
        BigDecimal totalAmount = spotAmount.add(inTransitAmount);
        
        // 周转天数
        int baseSpotTurnoverDays = (int) (20 + ThreadLocalRandom.current().nextInt(0, 20) / scaleFactor);
        int spotTurnoverDays = Math.max(15, Math.min(50, baseSpotTurnoverDays));
        int withInTransitTurnoverDays = spotTurnoverDays + ThreadLocalRandom.current().nextInt(10, 25);
        
        // 目标和达成率
        BigDecimal nrTarget = totalAmount.multiply(BigDecimal.valueOf(0.8 + ThreadLocalRandom.current().nextDouble() * 0.4));
        BigDecimal orderedAmount = totalAmount.multiply(BigDecimal.valueOf(0.7 + ThreadLocalRandom.current().nextDouble() * 0.3));
        BigDecimal achievementRate = orderedAmount.divide(nrTarget, 2, BigDecimal.ROUND_HALF_UP).multiply(BigDecimal.valueOf(100));
        achievementRate = achievementRate.min(BigDecimal.valueOf(100));
        
        // PV现货率
        BigDecimal pvSpotRate = BigDecimal.valueOf(85 + ThreadLocalRandom.current().nextDouble() * 12).setScale(2, BigDecimal.ROUND_HALF_UP);
        
        // 设置参数
        pstmt.setString(1, storeName);
        pstmt.setString(2, reportDate.format(DATE_FORMATTER));
        pstmt.setInt(3, spotQuantity);
        pstmt.setInt(4, inTransitQuantity);
        pstmt.setInt(5, totalQuantity);
        pstmt.setBigDecimal(6, spotAmount);
        pstmt.setBigDecimal(7, inTransitAmount);
        pstmt.setBigDecimal(8, totalAmount);
        pstmt.setInt(9, spotTurnoverDays);
        pstmt.setInt(10, withInTransitTurnoverDays);
        pstmt.setBigDecimal(11, nrTarget);
        pstmt.setBigDecimal(12, orderedAmount);
        pstmt.setBigDecimal(13, achievementRate);
        pstmt.setBigDecimal(14, pvSpotRate);
    }

    /**
     * 计算季节性因子
     */
    private double calculateSeasonalFactor(LocalDate date) {
        int month = date.getMonthValue();
        
        if (month <= 2) return 1.2;           // 春节期间
        else if (month == 6) return 1.25;     // 618购物节
        else if (month == 11) return 1.3;     // 双11购物节
        else if (month == 12) return 1.15;    // 年末促销
        else return 0.9 + ThreadLocalRandom.current().nextDouble() * 0.2; // 其他月份
    }

    /**
     * 计算年度趋势因子
     */
    private double calculateTrendFactor(LocalDate date) {
        int year = date.getYear();
        switch (year) {
            case 2023: return 0.9;   // 基准年
            case 2024: return 1.05;  // 5%增长
            case 2025: return 1.15;  // 15%增长
            default: return 1.0;
        }
    }

    /**
     * 验证生成的数据
     */
    private void validateData(Connection conn) throws SQLException {
        System.out.println("🔍 正在验证生成的数据...");
        
        try (Statement stmt = conn.createStatement()) {
            // 检查总记录数
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM supply_chain_report WHERE is_deleted = 0");
            rs.next();
            int totalCount = rs.getInt(1);
            
            // 检查日期范围
            rs = stmt.executeQuery("SELECT MIN(report_date), MAX(report_date) FROM supply_chain_report WHERE is_deleted = 0");
            rs.next();
            String minDate = rs.getString(1);
            String maxDate = rs.getString(2);
            
            // 检查店铺数量
            rs = stmt.executeQuery("SELECT COUNT(DISTINCT store_name) FROM supply_chain_report WHERE is_deleted = 0");
            rs.next();
            int storeCount = rs.getInt(1);
            
            System.out.println("📊 数据验证结果:");
            System.out.println("   总记录数: " + totalCount);
            System.out.println("   日期范围: " + minDate + " 至 " + maxDate);
            System.out.println("   店铺数量: " + storeCount);
            
            if (totalCount > 8000 && storeCount == 10) {
                System.out.println("✅ 数据验证通过");
            } else {
                System.out.println("❌ 数据验证失败");
            }
        }
    }
} 